--------------------------------------------------------
--  File created - Friday-October-03-2014   
--------------------------------------------------------
DROP TABLE "CASEDOC";
DROP TABLE "CASEHIST";
DROP TABLE "CASEINSTALLNOTE";
DROP TABLE "CASEMAST";
DROP TABLE "CASEMATCHTRACE";
DROP TABLE "CASEOUTLET";
DROP TABLE "IFDETAILS";
DROP TABLE "IFMCC";
DROP TABLE "MERCHANT";
DROP TABLE "NOTIFICATION";
DROP TABLE "NTFQUEUE";
DROP TABLE "NTFTEMPLATE";
DROP TABLE "OUTLET";
DROP TABLE "STATUSMAST";
DROP SEQUENCE "SEQ_CASEDOC";
DROP SEQUENCE "SEQ_CASEHIST";
DROP SEQUENCE "SEQ_CASEINSTALLNOTE";
DROP SEQUENCE "SEQ_CASEMAST";
DROP SEQUENCE "SEQ_CASEMATCHTRACE";
DROP SEQUENCE "SEQ_MERCHANT";
DROP SEQUENCE "SEQ_NOTIFICATION";
DROP SEQUENCE "SEQ_NTFQUEUE";
DROP SEQUENCE "SEQ_NTFTEMPLATE";
DROP SEQUENCE "SEQ_OUTLET";
DROP PACKAGE "STATUSACTION";
DROP PACKAGE BODY "STATUSACTION";
--------------------------------------------------------
--  DDL for Sequence SEQ_CASEDOC
--------------------------------------------------------

   CREATE SEQUENCE  "SEQ_CASEDOC"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 81 CACHE 20 NOORDER  NOCYCLE ;
--------------------------------------------------------
--  DDL for Sequence SEQ_CASEHIST
--------------------------------------------------------

   CREATE SEQUENCE  "SEQ_CASEHIST"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 121 CACHE 20 NOORDER  NOCYCLE ;
--------------------------------------------------------
--  DDL for Sequence SEQ_CASEINSTALLNOTE
--------------------------------------------------------

   CREATE SEQUENCE  "SEQ_CASEINSTALLNOTE"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 41 CACHE 20 NOORDER  NOCYCLE ;
--------------------------------------------------------
--  DDL for Sequence SEQ_CASEMAST
--------------------------------------------------------

   CREATE SEQUENCE  "SEQ_CASEMAST"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 141 CACHE 20 NOORDER  NOCYCLE ;
--------------------------------------------------------
--  DDL for Sequence SEQ_CASEMATCHTRACE
--------------------------------------------------------

   CREATE SEQUENCE  "SEQ_CASEMATCHTRACE"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE ;
--------------------------------------------------------
--  DDL for Sequence SEQ_MERCHANT
--------------------------------------------------------

   CREATE SEQUENCE  "SEQ_MERCHANT"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 181 CACHE 20 NOORDER  NOCYCLE ;
--------------------------------------------------------
--  DDL for Sequence SEQ_NOTIFICATION
--------------------------------------------------------

   CREATE SEQUENCE  "SEQ_NOTIFICATION"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 101 CACHE 20 NOORDER  NOCYCLE ;
--------------------------------------------------------
--  DDL for Sequence SEQ_NTFQUEUE
--------------------------------------------------------

   CREATE SEQUENCE  "SEQ_NTFQUEUE"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 81 CACHE 20 NOORDER  NOCYCLE ;
--------------------------------------------------------
--  DDL for Sequence SEQ_NTFTEMPLATE
--------------------------------------------------------

   CREATE SEQUENCE  "SEQ_NTFTEMPLATE"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 61 CACHE 20 NOORDER  NOCYCLE ;
--------------------------------------------------------
--  DDL for Sequence SEQ_OUTLET
--------------------------------------------------------

   CREATE SEQUENCE  "SEQ_OUTLET"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 141 CACHE 20 NOORDER  NOCYCLE ;
--------------------------------------------------------
--  DDL for Table CASEDOC
--------------------------------------------------------

  CREATE TABLE "CASEDOC" 
   (	"CD_ID" NUMBER(16,0), 
	"CD_CASE_ID" NUMBER(16,0), 
	"CD_NAME" VARCHAR2(200), 
	"CD_DATA" BLOB, 
	"CD_CREATED_DATE" DATE, 
	"CD_CREATED_BY" VARCHAR2(100), 
	"CD_TYPE" VARCHAR2(100), 
	"CD_LENGTH" NUMBER(16,0), 
	"CD_REF_NO" VARCHAR2(100)
   ) ;
--------------------------------------------------------
--  DDL for Table CASEHIST
--------------------------------------------------------

  CREATE TABLE "CASEHIST" 
   (	"CH_ID" NUMBER(16,0), 
	"CH_CASE_ID" NUMBER(16,0), 
	"CH_ACTION" VARCHAR2(50), 
	"CH_STATUS" VARCHAR2(50), 
	"CH_LAST_STATUS" VARCHAR2(50), 
	"CH_NOTE" VARCHAR2(4000), 
	"CH_CREATED_DATE" DATE, 
	"CH_CREATED_BY" VARCHAR2(100), 
	"CH_ASSIGNED_TO" VARCHAR2(4000)
   ) ;
--------------------------------------------------------
--  DDL for Table CASEINSTALLNOTE
--------------------------------------------------------

  CREATE TABLE "CASEINSTALLNOTE" 
   (	"IN_ID" NUMBER(16,0), 
	"IN_CASE_ID" NUMBER(16,0), 
	"IN_OUTLET_ID" NUMBER(16,0), 
	"IN_CREATED_BY" VARCHAR2(100), 
	"IN_CREATED_DATE" DATE, 
	"IN_REMARK" VARCHAR2(4000), 
	"IN_STATUS" VARCHAR2(20), 
	"IN_RESCHEDULE_DATE" VARCHAR2(20)
   ) ;
--------------------------------------------------------
--  DDL for Table CASEMAST
--------------------------------------------------------

  CREATE TABLE "CASEMAST" 
   (	"C_ID" NUMBER(16,0), 
	"C_MERCHANT_ID" NUMBER(16,0), 
	"C_ACQUIRER" VARCHAR2(100), 
	"C_MERCHANT_DETAILS" CLOB, 
	"C_MATCH_TRACE" CLOB, 
	"C_AMEX_AUTH_SIGN" CLOB, 
	"C_ACTION" VARCHAR2(50), 
	"C_STATUS" VARCHAR2(50), 
	"C_CREATED_DATE" DATE, 
	"C_CREATED_BY" VARCHAR2(100), 
	"C_MODIFIED_DATE" DATE, 
	"C_MODIFIED_BY" VARCHAR2(100), 
	"C_TYPE" VARCHAR2(20), 
	"C_GROUP_CODE" VARCHAR2(20), 
	"C_OTHER_OPT" CLOB, 
	"C_REFERENCE_NO" VARCHAR2(20), 
	"C_COUNTRY_CODE" VARCHAR2(3), 
	"C_SUBMIT_DATE" DATE, 
	"C_DOCUMENT_CHECKLIST" CLOB
   ) ;
--------------------------------------------------------
--  DDL for Table CASEMATCHTRACE
--------------------------------------------------------

  CREATE TABLE "CASEMATCHTRACE" 
   (	"CM_ID" NUMBER(16,0), 
	"CM_CASE_ID" NUMBER(16,0), 
	"CM_DATA" CLOB, 
	"CM_STATUS" VARCHAR2(20), 
	"CM_CREATED_BY" VARCHAR2(100), 
	"CM_CREATED_DATE" DATE, 
	"CM_MODIFIED_BY" VARCHAR2(100), 
	"CM_MODIFIED_DATE" DATE
   ) ;
--------------------------------------------------------
--  DDL for Table CASEOUTLET
--------------------------------------------------------

  CREATE TABLE "CASEOUTLET" 
   (	"CO_CASE_ID" NUMBER(16,0), 
	"CO_OUTLET_ID" NUMBER(16,0), 
	"CO_OUTLET_DETAILS" CLOB, 
	"CO_ESA_REF_NO" VARCHAR2(30), 
	"CO_NUM_TERMINAL" VARCHAR2(4), 
	"CO_TRM_RENTAL" VARCHAR2(4), 
	"CO_TRM_OWNER" VARCHAR2(50), 
	"CO_TRM_DEPOSIT_WIRED" NUMBER(16,2), 
	"CO_TRM_DEPOSIT_WIRELESS" NUMBER(16,2), 
	"CO_TRM_RENTAL_DEPOSIT_WIRED" NUMBER(16,2), 
	"CO_TRM_RENTAL_DEPOSIT_WIRELESS" NUMBER(16,2), 
	"CO_TRM_RENTAL_ADVANCE" NUMBER(16,2), 
	"CO_CHEQUE_AMT" NUMBER(16,2), 
	"CO_CHEQUE_ISSUER" VARCHAR2(100), 
	"CO_CHEQUE_NO" VARCHAR2(20), 
	"CO_CHEQUE_DATE" DATE, 
	"CO_MDR_RATE" CLOB, 
	"CO_STMT_OPTION" CLOB, 
	"CO_PYMT_OPTION" CLOB, 
	"CO_ADDRESS" CLOB, 
	"CO_TERMINAL_ID" VARCHAR2(30), 
	"CO_TERMINAL_ID_NO" VARCHAR2(20), 
	"CO_TERMINAL_INSTALL_DATE" VARCHAR2(20), 
	"CO_OTHER_INFO" CLOB, 
	"CO_ADDRESS_2" CLOB, 
	"CO_ADDRESS_3" CLOB, 
	"CO_RECOMMENDATION" CLOB, 
	"CO_POS_FORM" CLOB, 
	"CO_ESAPL" CLOB, 
	"CO_EZY" CLOB, 
	"CO_INSTALLATION" CLOB
   ) ;
--------------------------------------------------------
--  DDL for Table IFDETAILS
--------------------------------------------------------

  CREATE TABLE "IFDETAILS" 
   (	"IF_ID" NUMBER, 
	"IF_TYPE" VARCHAR2(100), 
	"IF_VLN" NUMBER, 
	"IF_VLH" NUMBER, 
	"IF_VLM" NUMBER, 
	"IF_VLEC" NUMBER, 
	"IF_VFN" NUMBER, 
	"IF_VFH" NUMBER, 
	"IF_VFM" NUMBER, 
	"IF_VFEC" NUMBER, 
	"IF_MCLN" NUMBER, 
	"IF_MCLH" NUMBER, 
	"IF_MCLM" NUMBER, 
	"IF_MCLEC" NUMBER, 
	"IF_MCFN" NUMBER, 
	"IF_MCFH" NUMBER, 
	"IF_MCFM" NUMBER, 
	"IF_MCFEC" NUMBER
   ) ;
--------------------------------------------------------
--  DDL for Table IFMCC
--------------------------------------------------------

  CREATE TABLE "IFMCC" 
   (	"IFM_CODE" VARCHAR2(20), 
	"IFM_IFID" NUMBER
   ) ;
--------------------------------------------------------
--  DDL for Table MERCHANT
--------------------------------------------------------

  CREATE TABLE "MERCHANT" 
   (	"M_ID" NUMBER(16,0), 
	"M_NAME" VARCHAR2(100), 
	"M_OWNERSHIP_STATUS" VARCHAR2(30), 
	"M_REG_NO" VARCHAR2(30), 
	"M_DESC" VARCHAR2(100), 
	"M_MCC_CODE" VARCHAR2(20), 
	"M_PAID_UP_CAPITAL" NUMBER(16,2), 
	"M_YEAR_IN_BUSINESS" VARCHAR2(20), 
	"M_RECALL_BARCODE" VARCHAR2(30), 
	"M_AVG_TICKET_SIZE" VARCHAR2(30), 
	"M_CURR_BANK" VARCHAR2(100), 
	"M_CURR_NO" VARCHAR2(20), 
	"M_CURR_AVG_DEPOSIT" NUMBER(16,2), 
	"M_CURR_DTL_DEPOSIT" CLOB, 
	"M_AMEX_AUTH_SIGN" CLOB, 
	"M_MATCH_TRACE" CLOB, 
	"M_ADDRESS" CLOB, 
	"M_CREATED_DATE" DATE, 
	"M_CREATED_BY" VARCHAR2(100), 
	"M_MODIFIED_DATE" DATE, 
	"M_MODIFIED_BY" VARCHAR2(100)
   ) ;
--------------------------------------------------------
--  DDL for Table NOTIFICATION
--------------------------------------------------------

  CREATE TABLE "NOTIFICATION" 
   (	"N_ID" NUMBER(16,0), 
	"N_USER_ID" VARCHAR2(65), 
	"N_CASE_ID" VARCHAR2(16), 
	"N_TYPE" VARCHAR2(50), 
	"N_CONTENT" CLOB, 
	"N_CREATED_DATE" DATE, 
	"N_CREATED_BY" VARCHAR2(100), 
	"N_HIST_ID" NUMBER(16,0), 
	"N_STATUS" VARCHAR2(20) DEFAULT 'Active', 
	"N_MODIFIED_DATE" DATE, 
	"N_MODIFIED_BY" VARCHAR2(100)
   ) ;
--------------------------------------------------------
--  DDL for Table NTFQUEUE
--------------------------------------------------------

  CREATE TABLE "NTFQUEUE" 
   (	"NQ_ID" NUMBER(16,0), 
	"NQ_TO" VARCHAR2(100), 
	"NQ_TYPE" VARCHAR2(50), 
	"NQ_SUBJECT" CLOB, 
	"NQ_BODY" CLOB, 
	"NQ_STATUS" VARCHAR2(20) DEFAULT 'Pending', 
	"NQ_SENT_DATE" DATE, 
	"NQ_STATUS_MSG" VARCHAR2(1000), 
	"NQ_TRY_DATE" DATE, 
	"NQ_TRY" NUMBER(3,0) DEFAULT 0, 
	"NQ_CREATED_DATE" DATE, 
	"NQ_CREATED_BY" VARCHAR2(100)
   ) ;
--------------------------------------------------------
--  DDL for Table NTFTEMPLATE
--------------------------------------------------------

  CREATE TABLE "NTFTEMPLATE" 
   (	"NT_ID" NUMBER(16,0), 
	"NT_CHANNEL" VARCHAR2(20), 
	"NT_SUBJECT" CLOB, 
	"NT_TEMPLATE" CLOB, 
	"NT_CREATED_DATE" DATE, 
	"NT_CREATED_BY" VARCHAR2(100), 
	"NT_MODIFIED_DATE" DATE, 
	"NT_MODIFIED_BY" VARCHAR2(100)
   ) ;
--------------------------------------------------------
--  DDL for Table OUTLET
--------------------------------------------------------

  CREATE TABLE "OUTLET" 
   (	"O_ID" NUMBER(16,0), 
	"O_MERCHANT_ID" NUMBER(16,0), 
	"O_NAME" VARCHAR2(100), 
	"O_REG_NAME" VARCHAR2(100), 
	"O_REG_NO" VARCHAR2(30), 
	"O_LICENSE_NO" VARCHAR2(30), 
	"O_LICENSE_TYPE" VARCHAR2(30), 
	"O_LICENSE_ISSUER" VARCHAR2(50), 
	"O_LICENSE_STATE" VARCHAR2(50), 
	"O_LICENSE_EXPIRY" VARCHAR2(30), 
	"O_RISK" VARCHAR2(20), 
	"O_CT_NAME" VARCHAR2(100), 
	"O_CT_DESIGNATION" VARCHAR2(100), 
	"O_CT_EMAIL" VARCHAR2(100), 
	"O_CT_TEL" VARCHAR2(20), 
	"O_CT_FAX" VARCHAR2(20), 
	"O_ADDRESS" CLOB, 
	"O_MDR_RATE" CLOB, 
	"O_STMT_OPTION" CLOB, 
	"O_PYMT_OPTION" CLOB, 
	"O_CREATED_DATE" DATE, 
	"O_CREATED_BY" VARCHAR2(100), 
	"O_MODIFIED_DATE" DATE, 
	"O_MODIFIED_BY" VARCHAR2(100), 
	"O_OTHER_INFO" CLOB, 
	"O_ADDRESS_2" CLOB, 
	"O_ADDRESS_3" CLOB, 
	"O_ESAPL" CLOB, 
	"O_EZY" CLOB
   ) ;
--------------------------------------------------------
--  DDL for Table STATUSMAST
--------------------------------------------------------

  CREATE TABLE "STATUSMAST" 
   (	"S_CURRENT" VARCHAR2(50), 
	"S_ACTION" VARCHAR2(50), 
	"S_NEXT" VARCHAR2(50), 
	"S_GROUP" VARCHAR2(50)
   ) ;
--------------------------------------------------------
--  DDL for Index CASEHIST_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "CASEHIST_PK" ON "CASEHIST" ("CH_ID") 
  ;
--------------------------------------------------------
--  DDL for Index CASEINSTALLNOTE_INDEX1
--------------------------------------------------------

  CREATE INDEX "CASEINSTALLNOTE_INDEX1" ON "CASEINSTALLNOTE" ("IN_CASE_ID", "IN_OUTLET_ID") 
  ;
--------------------------------------------------------
--  DDL for Index CASEINSTALLNOTE_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "CASEINSTALLNOTE_PK" ON "CASEINSTALLNOTE" ("IN_ID") 
  ;
--------------------------------------------------------
--  DDL for Index CASEMAST_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "CASEMAST_PK" ON "CASEMAST" ("C_ID") 
  ;
--------------------------------------------------------
--  DDL for Index CASEMAST_REF_IDX
--------------------------------------------------------

  CREATE UNIQUE INDEX "CASEMAST_REF_IDX" ON "CASEMAST" ("C_REFERENCE_NO") 
  ;
--------------------------------------------------------
--  DDL for Index CASEMATCHTRACE_INDEX_CASEID
--------------------------------------------------------

  CREATE INDEX "CASEMATCHTRACE_INDEX_CASEID" ON "CASEMATCHTRACE" ("CM_CASE_ID") 
  ;
--------------------------------------------------------
--  DDL for Index CASEMATCHTRACE_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "CASEMATCHTRACE_PK" ON "CASEMATCHTRACE" ("CM_ID") 
  ;
--------------------------------------------------------
--  DDL for Index CASEOUTLET_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "CASEOUTLET_PK" ON "CASEOUTLET" ("CO_CASE_ID", "CO_OUTLET_ID") 
  ;
--------------------------------------------------------
--  DDL for Index IFDETAILS_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "IFDETAILS_PK" ON "IFDETAILS" ("IF_ID") 
  ;
--------------------------------------------------------
--  DDL for Index IFMCC_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "IFMCC_PK" ON "IFMCC" ("IFM_CODE") 
  ;
--------------------------------------------------------
--  DDL for Index MERCHANT_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "MERCHANT_PK" ON "MERCHANT" ("M_ID") 
  ;
--------------------------------------------------------
--  DDL for Index NOTIFICATION_INDEX1
--------------------------------------------------------

  CREATE INDEX "NOTIFICATION_INDEX1" ON "NOTIFICATION" ("N_USER_ID", "N_CASE_ID", "N_HIST_ID") 
  ;
--------------------------------------------------------
--  DDL for Index NOTIFICATION_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "NOTIFICATION_PK" ON "NOTIFICATION" ("N_ID") 
  ;
--------------------------------------------------------
--  DDL for Index STATUSMAST_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "STATUSMAST_PK" ON "STATUSMAST" ("S_CURRENT", "S_ACTION", "S_NEXT") 
  ;
--------------------------------------------------------
--  DDL for Index CASEHIST_INDEX_CASEID
--------------------------------------------------------

  CREATE INDEX "CASEHIST_INDEX_CASEID" ON "CASEHIST" ("CH_CASE_ID") 
  ;
--------------------------------------------------------
--  DDL for Index CASEDOC_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "CASEDOC_PK" ON "CASEDOC" ("CD_ID") 
  ;
--------------------------------------------------------
--  DDL for Index CASEDOC_INDEX_CASEID
--------------------------------------------------------

  CREATE INDEX "CASEDOC_INDEX_CASEID" ON "CASEDOC" ("CD_CASE_ID") 
  ;
--------------------------------------------------------
--  DDL for Trigger TRIGGER_CASEDOC_INS
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "TRIGGER_CASEDOC_INS" 
BEFORE INSERT ON CASEDOC FOR EACH ROW 
DECLARE
  tempId CASEDOC.CD_ID%TYPE;
  
BEGIN
  
  select SEQ_CASEDOC.nextval into tempId from dual;  
  :NEW.CD_ID := tempId;
  
END;
/
ALTER TRIGGER "TRIGGER_CASEDOC_INS" ENABLE;
--------------------------------------------------------
--  DDL for Trigger TRIGGER_CASEHIST_INS
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "TRIGGER_CASEHIST_INS" 
BEFORE INSERT ON CASEHIST FOR EACH ROW
DECLARE
  histId CASEHIST.CH_ID%TYPE;
  
BEGIN
  
  select SEQ_CASEHIST.nextval into histId from dual;  
  :NEW.CH_ID := histId;
  
END;
/
ALTER TRIGGER "TRIGGER_CASEHIST_INS" ENABLE;
--------------------------------------------------------
--  DDL for Trigger TRIGGER_CASEINSTALLNOTE_INS
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "TRIGGER_CASEINSTALLNOTE_INS" 
BEFORE INSERT ON CASEINSTALLNOTE FOR EACH ROW  
DECLARE
  tempId CASEINSTALLNOTE.IN_ID%TYPE;
BEGIN
  select SEQ_CASEINSTALLNOTE.nextval into tempId from dual;  
  :NEW.IN_ID := tempId;
END;
/
ALTER TRIGGER "TRIGGER_CASEINSTALLNOTE_INS" ENABLE;
--------------------------------------------------------
--  DDL for Trigger TRIGGER_CASEMAST_INS
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "TRIGGER_CASEMAST_INS" 
BEFORE INSERT ON CASEMAST FOR EACH ROW
DECLARE
  caseID CASEMAST.C_ID%TYPE;
  
BEGIN

  select SEQ_CASEMAST.nextval into caseID from dual;  
  :NEW.C_ID := caseID;
  
END;
/
ALTER TRIGGER "TRIGGER_CASEMAST_INS" ENABLE;
--------------------------------------------------------
--  DDL for Trigger TRIGGER_CASEMATCHTRACE_INS
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "TRIGGER_CASEMATCHTRACE_INS" 
BEFORE INSERT ON CASEMATCHTRACE FOR EACH ROW 
DECLARE
  tempId CASEMATCHTRACE.CM_ID%TYPE;
BEGIN
  select SEQ_CASEMATCHTRACE.nextval into tempId from dual;  
  :NEW.CM_ID := tempId;
END;
/
ALTER TRIGGER "TRIGGER_CASEMATCHTRACE_INS" ENABLE;
--------------------------------------------------------
--  DDL for Trigger TRIGGER_MERCHANT_INS
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "TRIGGER_MERCHANT_INS" 
BEFORE INSERT ON MERCHANT FOR EACH ROW
DECLARE
  merchantId MERCHANT.M_ID%TYPE;
  
BEGIN

  select SEQ_MERCHANT.nextval into merchantId from dual;  
  :NEW.M_ID := merchantId;
  
END;
/
ALTER TRIGGER "TRIGGER_MERCHANT_INS" ENABLE;
--------------------------------------------------------
--  DDL for Trigger TRIGGER_NOTIFICATION_INS
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "TRIGGER_NOTIFICATION_INS" 
BEFORE INSERT ON NOTIFICATION FOR EACH ROW
DECLARE
  notificationId NOTIFICATION.N_ID%TYPE;
  
BEGIN

  select SEQ_NOTIFICATION.nextval into notificationId from dual;  
  :NEW.N_ID := notificationId;
  
END;
/
ALTER TRIGGER "TRIGGER_NOTIFICATION_INS" ENABLE;
--------------------------------------------------------
--  DDL for Trigger TRIGGER_NTFQUEUE_INS
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "TRIGGER_NTFQUEUE_INS" 
BEFORE INSERT ON NTFQUEUE FOR EACH ROW
DECLARE
  queueId NTFQUEUE.NQ_ID%TYPE;
  
BEGIN

  select SEQ_NTFQUEUE.nextval into queueId from dual;  
  :NEW.NQ_ID := queueId;
  
END;
/
ALTER TRIGGER "TRIGGER_NTFQUEUE_INS" ENABLE;
--------------------------------------------------------
--  DDL for Trigger TRIGGER_NTFTEMPLATE_INS
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "TRIGGER_NTFTEMPLATE_INS" 
BEFORE INSERT ON NTFTEMPLATE FOR EACH ROW
DECLARE
  templateId NTFTEMPLATE.NT_ID%TYPE;
  
BEGIN

  select SEQ_NTFTEMPLATE.nextval into templateId from dual;  
  :NEW.NT_ID := templateId;
  
END;
/
ALTER TRIGGER "TRIGGER_NTFTEMPLATE_INS" ENABLE;
--------------------------------------------------------
--  DDL for Trigger TRIGGER_OUTLET_INS
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "TRIGGER_OUTLET_INS" 
BEFORE INSERT ON OUTLET FOR EACH ROW
DECLARE
  tempId OUTLET.O_ID%TYPE;
  
BEGIN
  
  select SEQ_OUTLET.nextval into tempId from dual;  
  :NEW.O_ID := tempId;
  
END;
/
ALTER TRIGGER "TRIGGER_OUTLET_INS" ENABLE;
--------------------------------------------------------
--  DDL for Package STATUSACTION
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE "STATUSACTION" AS 

  FUNCTION action_next(case_id NUMBER, action VARCHAR2, note IN VARCHAR2, assigned_to in VARCHAR2, user_id VARCHAR2) RETURN NUMBER;

END STATUSACTION;

/
--------------------------------------------------------
--  DDL for Package Body STATUSACTION
--------------------------------------------------------

  CREATE OR REPLACE PACKAGE BODY "STATUSACTION" AS

  FUNCTION action_next(case_id NUMBER, action VARCHAR2, note IN VARCHAR2, assigned_to in VARCHAR2, user_id VARCHAR2) RETURN NUMBER
  AS 
    current_status STATUSMAST.S_CURRENT%TYPE;
    next_status STATUSMAST.S_NEXT%TYPE;
    nullable_note CASEHIST.CH_NOTE%TYPE;
    nullable_assigned_to CASEHIST.CH_ASSIGNED_TO%TYPE;
    hist_id CASEHIST.CH_ID%TYPE;
  BEGIN 
    
    nullable_note := note;
    if length(note) <= 0 then
      nullable_note := null;
    end if;
    
    nullable_assigned_to := assigned_to;
    if length(assigned_to) <= 0 then
      nullable_assigned_to := null;
    end if;
    
    select c_status into current_status 
    from casemast
    where c_id = case_id;
    
    select s_next into next_status from statusmast
    where (s_current = current_status or s_current = '*')
      and s_action = action;
    
    if next_status = '*' then
      next_status := current_status;
    end if;
    
    insert into casehist(ch_case_id, ch_action, ch_status, ch_last_status, 
      ch_note, ch_assigned_to, ch_created_date, ch_created_by)
    values(case_id, action, next_status, current_status,
      nullable_note, nullable_assigned_to, sysdate, user_id);
    
    select seq_casehist.currval into hist_id from dual;
    
    update casemast set c_action = action,
      c_status = next_status,
      c_modified_date = sysdate,
      c_modified_by = user_id
    where c_id = case_id;
    
    if current_status = 'PUT UP' then
      update casemast set c_submit_date = sysdate
      where c_id = case_id
        and c_submit_date is null;
    end if;
    
    return hist_id;
  
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      return -1;
  END;

END STATUSACTION;

/
